package DataBase;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Vector;

public class AddNewOrder {
	private Vector<DataObjects.Order> data;
	private Vector<Integer> ids;
	private Vector<DataObjects.Order> orders;
	private Vector<Vector<DataObjects.Order>> sortedOrders;

	public AddNewOrder(Vector<DataObjects.Order> orders) {
		this.data = orders;
	}

	public Vector<DataObjects.Order> getSavedOrders() {
		return this.orders;
	}

	public Vector<Integer> getInsertedIDs() {
		return this.ids;
	}

	public boolean Add() throws SQLException {
		SQLConnection sc = new SQLConnection();
		this.ids = new Vector<Integer>();
		this.orders = new Vector<DataObjects.Order>();
		this.sortedOrders = new Vector<Vector<DataObjects.Order>>();

		boolean ok = false;
		for (DataObjects.Order order : this.data) {
			
				//ok = false;
				for (int i = 0; i < sortedOrders.size(); i++) {
					if (order.getSupplier_id().equals(sortedOrders.get(i).get(0)
							.getSupplier_id())) {
						sortedOrders.get(i).add(order);
						
						i = sortedOrders.size();
						ok = true;
					}
				}
				
				if (ok == false) {
					Vector<DataObjects.Order> v = new Vector<DataObjects.Order>();
					v.add(order);
					sortedOrders.add(v);
				}
		}

		try {
			System.out.println("Starting inserts \n Suppliers count is: " + sortedOrders.size());
			sc.getConnection().setAutoCommit(false);
			PreparedStatement ps;
			int orderId = 0;

			for (int i = 0; i < sortedOrders.size(); i++) {

				ps = sc.getConnection()
						.prepareStatement(
								"INSERT INTO orders(supplier_id, date, time, status) VALUES(?,CURRENT_DATE,CURRENT_TIME,?);");
				ps.setString(1, sortedOrders.get(i).get(0).getSupplier_id()
						.toString());
				ps.setInt(2, 1);

				ps.executeUpdate();
				ps.close();
				
				ps = sc.getConnection().prepareStatement(
						"SELECT LAST_INSERT_ID();");
				ResultSet res = ps.executeQuery();
				while (res.next()){
					orderId = res.getInt(1);
					ids.add(orderId);
				}
				ps.close();

				for (int j = 0; j < sortedOrders.get(i).size(); j++) {
					ps = sc.getConnection()
							.prepareStatement(
									"INSERT INTO items_in_order(order_id, item_id, amount, price) VALUES(?,?,?,?);");
					ps.setInt(1, orderId);
					ps.setString(2, sortedOrders.get(i).get(j).getItem_id().toString());
					ps.setFloat(3, sortedOrders.get(i).get(j).getAmount());
					ps.setFloat(4, sortedOrders.get(i).get(j).getPrice());

					ps.executeUpdate();
					ps.close();
				}
			}

//			String sql = "SELECT o.order_id as order_id, o.amount as amount, o.price as price, o.date as date, i.Iname as item_name, "
//					+ "i.IUnit_of_measure as measure, s.suppName as supp_name, s.suppFax as fax, s.suppCity as supp_city,s.suppStreet as supp_street, "
//					+ "s.suppPhoneNo as phone1, s.suppPhoneNo2 as phone2, s.suppCellNo as cell_phone "
//					+ "FROM orders o JOIN item i ON i.Iid = o.item_id JOIN supplier s ON s.suppID = o.supplier_id WHERE order_id = "
//					+ ids.get(0);
//
//			for (int id : ids) {
//				if (id != ids.get(0))
//					sql += " OR order_id = " + id;
//			}
//			sql += ";";
//
//			ps = sc.getConnection().prepareStatement(sql);
//			ResultSet rs = ps.executeQuery();
//			ResultSetMetaData rsmd = rs.getMetaData();
//			Vector<String> order;
//
//			while (rs.next()) {
//				order = new Vector<String>();
//				for (int i = 1; i < rsmd.getColumnCount(); i++) {
//					int type = rsmd.getColumnType(i);
//					String name = rsmd.getColumnName(i);
//
//					order.add(String.valueOf(rs.getInt("order_id")));
//					order.add(rs.getTimestamp("date").toString());
//					order.add(String.valueOf(rs.getFloat("price")));
//					order.add(String.valueOf(rs.getFloat("amount")));
//					order.add(rs.getString("item_name"));
//					order.add(rs.getString("measure"));
//					order.add(rs.getString("supp_name"));
//					order.add(rs.getString("supp_city"));
//					order.add(rs.getString("supp_street"));
//					order.add(rs.getString("phone1"));
//					order.add(rs.getString("phone2"));
//					order.add(rs.getString("cell_phone"));
//				}
//				// System.out.println(order);
//				this.orders.add(order);
//			}
//			ps.close();
			sc.getConnection().commit();
			sc.getConnection().close();
		} catch (SQLException e) {
			sc.getConnection().rollback();
			e.printStackTrace();
			return false;
		}

		return true;
	}
}
